﻿Imports System.Data.OleDb
Public Class Ladger
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Dim totaldebit As Double
    Dim totalcredit As Double
    Dim insertflag As Boolean = False
    Private Sub txtamount_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtamount.KeyDown
        If e.KeyCode = Keys.Enter Then
            If cmbparty.Text = "" Then
                MsgBox("Not party selected", MsgBoxStyle.Information)
                Exit Sub
            End If
            If Val(txtamount.Text) = 0 Then
                MsgBox("No amount specified", MsgBoxStyle.Information)
                Exit Sub
            End If
            rdb.Checked = True
            dgridladger.Rows.Add()
            Dim i As Integer = dgridladger.Rows.Count
            dgridladger.Item(0, i - 1).Value = cmbparty.Text
            dgridladger.Item(1, i - 1).Value = Format(txtdate.Value, "dd/MMM/yyyy")
            If rdb.Checked = True Then
                dgridladger.Item(2, i - 1).Value = rdb.Text & " " & txtnarration.Text
            Else : rdr.Checked = True
                dgridladger.Item(2, i - 1).Value = rdr.Text & " " & txtnarration.Text
            End If
            If rddebit.Checked = True Then
                dgridladger.Item(3, i - 1).Value = Val(txtamount.Text).ToString("#0.00")
            Else
                dgridladger.Item(4, i - 1).Value = Val(txtamount.Text).ToString("#0.00")
            End If
            clearall()
            cmbparty.Focus()
            filltotal()
        End If
    End Sub
    Private Sub loadwork()
        Dim str As String
        str = "select count(*) from Ladger"
        cmd = New OleDbCommand(str, con)
        If cmd.ExecuteScalar > 100 Then
            MsgBox("You demo limit for Ladger is over")
            btnadd.Enabled = False
        End If
    End Sub
    Private Sub fillparty()
        Dim str As String
        cmbparty.Items.Clear()
        str = "select customer_id,customer_name from customer_master order by customer_name"
        cmd = New OleDbCommand(str, con)
        dr = cmd.ExecuteReader
        While dr.Read
            cmbparty.Items.Add(dr(1).ToString)
        End While
    End Sub
    Private Sub clearall()
        txtamount.Text = "0.00"
        txtnarration.Text = ""
        cmbparty.SelectedIndex = -1
    End Sub
    Private Sub Ladger_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        rddebit.Checked = True
        fillparty()
        tblmain.Enabled = False
        dgridladger.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgridladger.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        loadwork()
    End Sub

    Private Sub txtamount_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtamount.KeyPress
        If Asc(e.KeyChar) = 46 Then
            If sender.text.contains(e.KeyChar) = True Then
                e.KeyChar = ""
            End If
        End If
        If IsNumeric(e.KeyChar) = False And Asc(e.KeyChar) <> 8 And Asc(e.KeyChar) <> 46 Then
            e.KeyChar = ""
        End If
    End Sub

    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
        Dim str As String
        Dim n As Long
        If btnadd.Text = "Add" Then
            btnadd.Text = "Save"
            tblmain.Enabled = True
            btnclose.Text = "Cancel"
            rdb.Checked = True
            cmbparty.Select()
        ElseIf btnadd.Text = "Save" Then
            If dgridladger.Rows.Count = 0 Then
                MsgBox("No record in sheet to save", MsgBoxStyle.Information)
                Exit Sub
            End If
            While dgridladger.Rows.Count > 0
                str = "select customer_id from customer_master where customer_name='" & dgridladger.Item(0, 0).Value & "'"
                cmd = New OleDbCommand(str, con)
                dr = cmd.ExecuteReader
                If dr.Read Then
                    n = Val(dr(0).ToString)
                End If
                str = "insert into ladger(accountid,Narration,damount,camount,billdate,todaydate) values(" & n & ",'" & dgridladger.Item(2, 0).Value & "'," & Val(dgridladger.Item(3, 0).Value) & "," & Val(dgridladger.Item(4, 0).Value) & ",#" & dgridladger.Item(1, 0).Value & "#,#" & Format(Today.Date, "dd/MMM/yyyy") & "#)"
                ' MsgBox(str)
                cmd = New OleDbCommand(str, con)
                If cmd.ExecuteNonQuery > 0 Then
                    dgridladger.Rows.RemoveAt(0)
                Else
                    MsgBox("Error while updation", MsgBoxStyle.Critical)
                    Exit Sub
                End If
            End While
            btnclose.Text = "Close"
            btnadd.Text = "Add"
            tblmain.Enabled = False
        End If
    End Sub

    Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
        If btnclose.Text = "Cancel" Then
            If MessageBox.Show("Are you sure you want to Cancel this sheet", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
                btnadd.Text = "Add"
                tblmain.Enabled = False
                btnclose.Text = "Close"
            End If
        ElseIf btnclose.Text = "Close" Then
            Me.Close()
        End If
    End Sub
    Private Sub dgridladger_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles dgridladger.EditingControlShowing
        If dgridladger.CurrentCell.ColumnIndex = 3 Or dgridladger.CurrentCell.ColumnIndex = 4 Then
            Dim tb As TextBox = CType(e.Control, TextBox)
            AddHandler tb.KeyPress, AddressOf txtrollno_keypress
            AddHandler tb.KeyUp, AddressOf txtamount_KeyUp
        End If
    End Sub

    Private Sub txtrollno_keypress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        If sender.text.contains(e.KeyChar) = True Then
            If Asc(e.KeyChar) = 46 Then
                e.KeyChar = ""
            End If
        End If
        If IsNumeric(e.KeyChar) = False And Asc(e.KeyChar) <> 8 And Asc(e.KeyChar) <> 46 Then
            e.KeyChar = ""
        End If
      
    End Sub

    Private Sub txtnarration_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtnarration.KeyPress
        If Asc(e.KeyChar) = 39 Then
            e.KeyChar = ""
        End If
    End Sub

    Private Sub cmbparty_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbparty.SelectedIndexChanged
        Try
            Dim str As String, ob As Object, dd As Date, i As Integer
            Dim camount As Double
            Dim d As Double
            str = "select cdate from consolidate where accountid=(select customer_id from customer_master where customer_name='" & cmbparty.Text & "')"
            cmd = New OleDbCommand(str, con)
            dr = cmd.ExecuteReader
            If dr.Read Then
                ob = dr(0).ToString
            Else
                ob = ""
            End If
            If ob.ToString = "" Then
                str = "select billdate,narration,damount,camount from ladger where accountid=(select customer_id from customer_master where customer_name='" & cmbparty.Text & "')"
            Else
                dd = ob
                txtdate.Value = dd
                str = "select billdate,narration,damount,camount from ladger where accountid=(select customer_id from customer_master where customer_name='" & cmbparty.Text & "') and billdate>#" & dd & "#"
            End If
            dgridfilldetail.Rows.Clear()
            Dim ob1 As Object
            If ob.ToString <> "" Then
                str = "select sum(camount)-sum(damount) from ladger where accountid=(select customer_id from customer_master where customer_name='" & cmbparty.Text & "') and billdate<=#" & dd & "#"
                cmd = New OleDbCommand(str, con)
                ob1 = cmd.ExecuteScalar
                If ob1.ToString = "" Then
                    camount = 0
                Else
                    camount = Val(ob1.ToString)
                    dgridfilldetail.Rows.Add()
                    dgridfilldetail.Item(0, i).Value = Format(dd, "dd/MMM/yyyy")
                    dgridfilldetail.Item(1, i).Value = "Previous credit balance"
                    dgridfilldetail.Item(3, i).Value = camount
                    dgridfilldetail.Item(4, i).Value = Val(dgridfilldetail.Item(2, i).Value)
                    i = i + 1
                End If
            End If
            If ob.ToString <> "" Then
                str = "select billdate,narration,damount,camount from ladger where accountid=(select customer_id from customer_master where customer_name='" & cmbparty.Text & "') and billdate>#" & dd & "#"
                cmd = New OleDbCommand(str, con)
                dr = cmd.ExecuteReader
                While dr.Read
                    dgridfilldetail.Rows.Add()
                    dgridfilldetail.Item(0, i).Value = Format(dr(0), "dd/MMM/yyyy")
                    dgridfilldetail.Item(1, i).Value = dr(1).ToString
                    d = Val(dr(2).ToString)
                    totaldebit = totaldebit + d
                    dgridfilldetail.Item(2, i).Value = d.ToString("#0.00")
                    d = 0
                    d = Val(dr(3).ToString)
                    totalcredit = totalcredit + d
                    dgridfilldetail.Item(3, i).Value = d.ToString("#0.00")
                    d = 0
                    If i = 0 Then
                        dgridfilldetail.Item(4, i).Value = Val(dgridfilldetail.Item(2, i).Value) - Val(dgridfilldetail.Item(3, i).Value)
                    Else
                        dgridfilldetail.Item(4, i).Value = (Val(dgridfilldetail.Item(4, i - 1).Value) + Val(dgridfilldetail.Item(2, i).Value)) - Val(dgridfilldetail.Item(3, i).Value)
                    End If
                    i = i + 1
                End While
            Else
                str = "select billdate,narration,damount,camount from ladger where accountid=(select customer_id from customer_master where customer_name='" & cmbparty.Text & "')"
                cmd = New OleDbCommand(str, con)
                dr = cmd.ExecuteReader
                While dr.Read
                    dgridfilldetail.Rows.Add()
                    dgridfilldetail.Item(0, i).Value = Format(dr(0), "dd/MMM/yyyy")
                    dgridfilldetail.Item(1, i).Value = dr(1).ToString
                    d = Val(dr(2).ToString)
                    totaldebit = totaldebit + d
                    dgridfilldetail.Item(2, i).Value = d.ToString("#0.00")
                    d = 0
                    d = Val(dr(3).ToString)
                    totalcredit = totalcredit + d
                    dgridfilldetail.Item(3, i).Value = d.ToString("#0.00")
                    d = 0
                    If i = 0 Then
                        dgridfilldetail.Item(4, i).Value = Val(dgridfilldetail.Item(2, i).Value) - Val(dgridfilldetail.Item(3, i).Value)
                    Else
                        dgridfilldetail.Item(4, i).Value = (Val(dgridfilldetail.Item(4, i - 1).Value) + Val(dgridfilldetail.Item(2, i).Value)) - Val(dgridfilldetail.Item(3, i).Value)
                    End If
                    i = i + 1
                End While
            End If
            dgridfilldetail.ClearSelection()
            filltotal()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub filltotal()
        Dim i As Integer
        Dim count As Integer
        count = dgridladger.Rows.Count
        lbltotaldebit.Text = 0
        lblcredittotal.Text = 0
        lbltotalclosing.Text = 0
        lbltotalcreditdetail.Text = 0
        lbltotaldebitdetail.Text = 0
        While i < count
            lbltotaldebit.Text = Val(lbltotaldebit.Text) + Val(dgridladger.Item(3, i).Value)
            lblcredittotal.Text = Val(lblcredittotal.Text) + Val(dgridladger.Item(4, i).Value)
            i = i + 1
        End While
        i = 0
        count = dgridfilldetail.Rows.Count
        While i < count
            lbltotaldebitdetail.Text = Val(lbltotaldebitdetail.Text) + Val(dgridfilldetail.Item(2, i).Value)
            lbltotalcreditdetail.Text = Val(lbltotalcreditdetail.Text) + Val(dgridfilldetail.Item(3, i).Value)
            i = i + 1
        End While
        lbltotalclosing.Text = Val(lbltotalcreditdetail.Text) - Val(lbltotaldebitdetail.Text)
    End Sub

    Private Sub txtamount_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
        If dgridladger.CurrentCell.ColumnIndex = 3 Then
            dgridladger.Item(3, dgridladger.CurrentRow.Index).Value = sender.text
            filltotal()
        ElseIf dgridladger.CurrentCell.ColumnIndex = 4 Then
            dgridladger.Item(4, dgridladger.CurrentRow.Index).Value = sender.text
            filltotal()
        End If
    End Sub

    Private Sub txtamount_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtamount.TextChanged

    End Sub

    Private Sub FlowLayoutPanel1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles FlowLayoutPanel1.Paint

    End Sub
End Class